
package com.bafa.jdbc;

import com.bafa.dbcache.StartupServlet;
import com.bafa.middle.betwixt.Utils;
import com.bafa.objects.player.Player;
import com.bafa.objects.Report;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;

import javax.xml.bind.*;
import com.bafa.objects.Match;
import com.bafa.objects.team.Team;
import com.bafa.objects.schema.*;
import com.bafa.objects.season.Season;

import java.io.*;

import org.apache.commons.betwixt.io.BeanWriter;

public class DBAccess {
public static void main(String[] args) {
	
	/*QueryObject query = new QueryObject("tempresults");
	
	
	UpdateObject update = new UpdateObject();
	update.addField("ID",new Integer(7));
	update.addField("GoalsA", new Integer(3));
	update.addField("GoalsB", new Integer(2));
	update.addField("UserId", "or");
	try{
		insert(query,update);
		commit();
	}catch(Exception e){e.printStackTrace();}finally{
		
	}*/
}

public Connection conn = null;
public DBAccess(){
	connect();
}
	public void commit(){
		try{
			//conn.commit();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			disconnect();
		}
	}
	
	private Connection connect(){
		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		}

		catch (Exception E) {
			System.err.println("Unable to load driver.");
			E.printStackTrace();
		}
		
		try{
			
			conn = DriverManager.getConnection("jdbc:mysql://"+StartupServlet.dbhost+"/"+StartupServlet.dbname+"?user="+StartupServlet.dbuser+"&password="+StartupServlet.dbpassword);
			//conn.setAutoCommit(false);
		}catch(Exception e){
			e.printStackTrace();
		}

		return conn;

	}
	
	public ResultSet count(QueryObject query)throws SQLException{
		
		Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
		
		return stmt.executeQuery(query.countString().trim());
	}
	public void delete(QueryObject query)throws Exception{
		
			ResultSet rs = query(query);
			while(rs.next()){
				rs.deleteRow();	
			}
			
		
	}
	
	public void deleteMatch(Match match)throws Exception{
		PreparedStatement updateTeam = conn.prepareStatement("delete from matchesbetwixt where id=?");
		updateTeam.setInt(1, match.getID());
		updateTeam.executeUpdate();
	}
	public void deleteReport(String id)throws Exception{
	    PreparedStatement updateTeam = conn.prepareStatement("delete from reports where id=?");
	    updateTeam.setString(1,id);
	    updateTeam.executeUpdate();
	}
	
	
	public void disconnect(){
		try
		{
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public ResultSet insert(QueryObject query, UpdateObject update)throws SQLException{
		
			ResultSet rs = query(query);
			rs.moveToInsertRow();
				while(update.hasNext()){
					Object value = update.nextValue();
					if(value instanceof String)
						rs.updateString(update.nextField(),(String)value);
					else if(value instanceof Integer)
						rs.updateInt(update.nextField(),((Integer)value).intValue());
					else if(value instanceof Date)
						rs.updateDate(update.nextField(),(Date)value);
					else if(value instanceof Time)
						rs.updateTime(update.nextField(),(Time)value);
					
					
				}
				rs.insertRow();
			return rs;
		
	}
	
	public ResultSet query(QueryObject query) throws SQLException{
	
		Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
		
		return stmt.executeQuery(query.getQueryString().trim());
	}
	
	
	
	public String retrieveDivisionName(int divisionId, int seasonId)throws Exception{
            PreparedStatement updateTeam = conn.prepareStatement("select * from divisions where divisionId=? and seasonId=?");
            updateTeam.setInt(1, divisionId);
        updateTeam.setInt(2, seasonId);
            ResultSet rs = updateTeam.executeQuery();
            String name = "";
            while(rs.next()){
                rs.getString("name");
            }
            return name;
    }
	public ResultSet retrieveMatches()throws Exception{
		PreparedStatement updateTeam = conn.prepareStatement("select * from matchesbetwixt");
		
		return updateTeam.executeQuery();
	}
        
        public ResultSet retrievePlayer(int id)throws Exception{
		        PreparedStatement player = conn.prepareStatement("select * from playerblobs where id=?");
		        player.setInt(1,id);
		        return player.executeQuery();
		}
	
    public ResultSet retrievePlayers()throws Exception{
            PreparedStatement updateTeam = conn.prepareStatement("select * from players");
            
            return updateTeam.executeQuery();
    }
    
	public ResultSet retrieveReports()throws Exception{
		PreparedStatement updateTeam = conn.prepareStatement("select * from reports");
		
		return updateTeam.executeQuery();
	}
	public ResultSet retrieveSeasons()throws Exception{
		PreparedStatement updateTeam = conn.prepareStatement("select * from seasonbetwixt order by id desc");
		
		return updateTeam.executeQuery();
	}
	
	public String retrieveReference(String id){
		String value = "";
		try{
			PreparedStatement reference = conn.prepareStatement("select * from reference where id=?");
		
		reference.setString(1,id);
		ResultSet result = reference.executeQuery();
		
		while(result.next()){
			value = result.getString("value");
		}
		}catch(Exception e){
			
		}
		return value;
	}
	public String retrievePhoto(String id){
		String value = "";
		try{
			PreparedStatement reference = conn.prepareStatement("select * from photos where id=?");
		
		reference.setString(1,id);
		ResultSet result = reference.executeQuery();
		
		while(result.next()){
			value = result.getString("photoUrl");
		}
		}catch(Exception e){
			
		}
		return value;
	}
	
	public ResultSet retrieveTeams()throws Exception{
		PreparedStatement updateTeam = conn.prepareStatement("select * from teambetwixts");
		
		return updateTeam.executeQuery();
	}
	
	public void rollback(boolean close){
		try{
			conn.rollback();
		}catch(Exception e){
			
		}finally{
			if(close)disconnect();
		}
	}
	
	public void storeMatch(com.bafa.objects.Match match)throws Exception{
		String xml = Utils.writeBetwixt("match",match);
        PreparedStatement updateTeam = conn.prepareStatement("insert into matchesbetwixt values(?,?)");
        updateTeam.setObject(2, xml.getBytes());  
        updateTeam.setInt(1, match.getID());
        updateTeam.executeUpdate();
		
	}
	
	
	
	public void storePlayer(Player player)throws Exception{
        String xml = Utils.writeBetwixt("player",player);
        PreparedStatement updateTeam = conn.prepareStatement("insert into players values(?,?)");
        updateTeam.setObject(2, xml.getBytes());  
        updateTeam.setString(1, player.getFirstname().replaceAll("'","\'").toUpperCase());
        updateTeam.executeUpdate();
    }
	
	public void storeReport(Report report)throws Exception{
            PreparedStatement updateTeam = conn.prepareStatement("insert into reports values(?,?,?,?,?,?,?,?,?)");
          
            updateTeam.setString(1, report.getReportId());
            updateTeam.setObject(2, report.getReport().getBytes());          
            updateTeam.setInt(3, report.getMatchId());
            updateTeam.setInt(4, report.getSeasonId());
            updateTeam.setInt(5, report.getTeamId());
            updateTeam.setString(6, report.getCategory());
            updateTeam.setString(7, report.getTitle());
            updateTeam.setString(8, report.getAuthor());
            updateTeam.setObject(9, null);
            updateTeam.executeUpdate();
    }
    public void storeSeason(Season season)throws Exception{
		String xml = Utils.writeBetwixt("season",season);
        PreparedStatement updateTeam = conn.prepareStatement("insert into seasonbetwixt values(?,?)");
        updateTeam.setObject(2, xml.getBytes());  
        updateTeam.setInt(1, season.getId());
        updateTeam.executeUpdate();
	}
	public void storeTeam(Team team)throws Exception{
		String xml = Utils.writeBetwixt("team",team);
        PreparedStatement updateTeam = conn.prepareStatement("insert into teambetwixts values(?,?)");
        updateTeam.setObject(2, xml.getBytes());  
        updateTeam.setInt(1, team.getId());
        updateTeam.executeUpdate();
	}
        
    public  ResultSet update(QueryObject query, UpdateObject update) throws Exception{
		
			ResultSet rs = query(query);
			while(rs.next()){
				while(update.hasNext()){
					Object value = update.nextValue();
					if(value instanceof String)
						rs.updateString(update.nextField(),(String)value);
					else if(value instanceof Integer)
						rs.updateInt(update.nextField(),((Integer)value).intValue());
					else if(value instanceof Date)
						rs.updateDate(update.nextField(),(Date)value);
					else if(value instanceof Double)
						rs.updateDouble(update.nextField(),(Double)value);
				}
				rs.updateRow();
			}
			return rs;
		
	}
    
    
    
    public void updateMatch(com.bafa.objects.Match match)throws Exception{
		
		PreparedStatement updateTeam = conn.prepareStatement("delete from matchesbetwixt where id=?");
		updateTeam.setInt(1, match.getID());
		updateTeam.executeUpdate();
		
		
		
		storeMatch(match);
	}
    
	public void updatePlayer(Player player)throws Exception{
            PreparedStatement updateTeam = conn.prepareStatement("delete from players where id=?");
            
            updateTeam.setString(1, player.getFirstname().replaceAll("'","\'"));
            updateTeam.executeUpdate();
            
            storePlayer(player);
    }
	public void updateReport(Report report)throws Exception{
        PreparedStatement updateTeam = conn.prepareStatement("delete from reports where id=?");
        
        updateTeam.setString(1, report.getReportId());
        updateTeam.executeUpdate();
        
        storeReport(report);
}
    public void updateSeason(Season season)throws Exception{
		PreparedStatement updateTeam = conn.prepareStatement("delete from seasonbetwixt where id=?");
		
		updateTeam.setInt(1, season.getId());
		updateTeam.executeUpdate();
		storeSeason(season);
		
	}
    
	
	  public void updateAccounts(int teamId,float total,float discounts,float fines,String comment)throws Exception{
			PreparedStatement accounts = conn.prepareStatement("delete from accounts where teamid=?");
			
			accounts.setInt(1, teamId);
			accounts.executeUpdate();
			
			PreparedStatement insertaccounts = conn.prepareStatement("insert into accounts (totalamount,totaldiscount,totalfines,comment,teamid) values (?,?,?,?,?)");
			insertaccounts.setDouble(1,total);
			insertaccounts.setDouble(2,discounts);
			insertaccounts.setDouble(3,fines);
			insertaccounts.setString(4,comment);
			insertaccounts.setInt(5,teamId);
			
			insertaccounts.executeUpdate();
		}
	  
	  
    public void updateTeam(Team team)throws Exception{
		PreparedStatement updateTeam = conn.prepareStatement("delete from teambetwixts where id=?");
		
		updateTeam.setInt(1, team.getId());
		updateTeam.executeUpdate();
		
		storeTeam(team);
	}
	
	public void updateReference(String id,String value)throws Exception{
		PreparedStatement updateTeam = conn.prepareStatement("update reference set value=? where id=?");
		
		updateTeam.setString(1, value);
		updateTeam.setString(2, id);
		updateTeam.executeUpdate();
	}
	
}
